"""
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：make_invoice.py
#日期：2018-10-22
#备注：
"""
import re

import xlrd
import xlwt
import os
from xlutils.copy import copy
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachomodule.zipfile_model import write_file


# 发票模板：需要修改的地方（定位发票具体要修改的地方）
def invoice(sample_file):
    data = xlrd.open_workbook(sample_file)
    table = data.sheets()[0]
    a = table.cell_value(7, 1)
    b = table.cell_value(8, 5)
    print(a, b)


# 获取发票需要的动态参数：第3行第2列B发票号invoice，第3行第8列H姓名name，9列I是地址address，
def get_excel_data(packing_file):
    data = xlrd.open_workbook(packing_file)
    table = data.sheets()[0]
    n_rows = table.nrows
    num = 0
    data = []
    for i in range(0, n_rows):  # 第三行
        row = []
        invoice_no = table.cell_value(i, 1)  # B
        name = table.cell_value(i, 7)  # H
        if invoice_no != '':
            detail = table.cell_value(i, 8)  # I=8
            detail = (detail.replace(",  EMAIL :", "TEST").replace("Email : ", "TEST").replace(", EMAIL :", "TEST")
                      .replace("TEL :", "TEST").replace("Tel : ", "TEST")
                      .replace(", ID TAX", "TEST").replace("Tax Id :", "TEST").replace("Tax Id : ","TEST")
                      .split("TEST"))
            print("处理后的信息：", detail)
            try:
                len(detail) == 3
            except:
                print(f"发票号：{invoice_no}格式不对！")
                continue
                # print("详细信息是：", detail)
            # 去除 开始和结束的空格
            address = detail[0].strip()
            print("地址是：", address)
            email = detail[2].strip()
            print("邮箱是：", email)
            telephone = detail[1].strip()
            print("电话是：", telephone)
            tax = detail[3].strip()
            print("ID TAX是：", email)
            num = num + 1
            row.append(invoice_no)
            row.append(name)
            row.append(address)  # TO
            row.append(tax)  # TAX ID
            row.append(email)  # EMAIL
            row.append(telephone)  # TEL
            data.append(row)

    print('总发票数量：', num)
    # print(data)
    return data


# TypeError: descriptor 'decode' requires a 'bytes' object but received a 'NoneType'
# F:\python_env\PaChong_env\lib\site-packages\\xlwt\UnicodeUtils.py
def make_excel(invoice_no, name, address, tax, email, telephone):
    width = 256 * 8  # 8个字符宽
    # 字体和格式
    font = xlwt.Font()
    font.height = 240  # 12号字体
    font.bold = True
    font.name = 'Times New Roman'
    style = xlwt.XFStyle()
    style.font = font
    names = ['Eucalipto', 'Henry', 'Manolo', 'Vizcainas', 'LUIS', 'JOSE', 'CROMO', 'HIDALGO', 'ESPINOZA', 'MM',
             '5 MAYO', 'CR COSS']
    zip_file = path + minus_date(0, 's') + '.zip'
    for invoice_type in names:
        if invoice_type in address:  # 关键字存在于完整地址中时
            sample_file = path + 'invoice_sample_' + invoice_type + '.xls'
            print(sample_file)
            invoice_name = invoice_type + '.xls'
            invoices = path + invoice_no + ' ' + invoice_name
            # S6221010G1  0开始，截取数量=相差数（4-2=2个字符）
            date = '20' + invoice_no[2:4] + '-' + invoice_no[4:6] + '-' + invoice_no[6:8]
            data = xlrd.open_workbook(sample_file, formatting_info=True)
            new_excel = copy(data)
            ws = new_excel.get_sheet(0)  # 获取第一个sheet
            first_col = ws.col(0)  # 第一列
            first_col.width = width  # 第一列宽
            ws.write(4, 8, date, style)  # 5I(4,8)-->DATE
            ws.write(8, 1, name, style)  # 9B(8,1)-->ATTN
            ws.write(9, 1, tax, style)  # 10B(9,1)-->TAX ID
            ws.write(10, 1, email, style)  # 11B(10,1)-->EMAIL
            ws.write(11, 1, telephone, style)  # 12B(11,1)-->TEL
            ws.write(11, 8, invoice_no, style)  # 12I(11,8) # INV#
            new_excel.save(invoices)
            write_file(zip_file, invoices, os.path.basename(invoices))
    print("发票完成！", zip_file)


def mk_invoices():
    packing_file = path + 'packing.xls'
    data = get_excel_data(packing_file)  # 获取所需参数
    for info in data:
        invoice_no = info[0]
        name = info[1]
        address = info[2]
        tax = info[3]
        email = info[4]
        telephone = info[5]
        make_excel(invoice_no, name, address, tax, email,telephone)


if __name__ == '__main__':
    path = 'F:\\PythonProject\\homework\\invoice\\'
    mk_invoices()
